/**
* Class to manage properties for DB
*
* Project: idbCrownInServer
* Author:  TM
* Date:    03/03/2013
*
* History
* ------------------
* 03/03/2013 - TM - Initial version.
*/
 
 
package DataModel;

import java.sql.Timestamp;
import java.util.TimeZone;
import java.text.*;

import Application.*;
import Util.*;

public class libDatabaseProperties
{
  /** Database type constants - typeDbServer */
  public static final int NO_DB           = -1;  // App without DB connection
  public static final int JDBC_FOR_UPDATE =  0;  // Driver supporting "FOR UPDATE"
  public static final int ORACLE          =  1;
  public static final int MSSQL           =  2;
    /** Date format string constants */
  protected static final String NATIVE_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss.SSS";
  /** Database type - determines how record-lock query is built & main connection JDBC URL */
  protected int typeDbServer;
  /** Database (hard) sorting is compatible with Java sorting rules */
  public boolean canHardSort;
  /** Rules to sort database fields */
  public RuleBasedCollator dbRuleCollator;
  /** Maximum length for string fields */
  protected Integer maxLengthChar;
  /** SQL string concatenation operator */
  protected String dbConcat;
  /** Function prefix for non-string data type object to string conversion */
  protected String toCharFuncBegin;
  /** Function prefix for string data type object to integer number conversion */
  protected String toIntFuncBegin;
  /** Decimal representation maximum, minimum and rounding figures & significant margin + normalizer */
  protected Double  dbMaxDouble;
  protected Double  dbMinDouble;
  protected Integer dbMaxSignificant;
  protected Integer dbSignifMargin;
  /** Boolean YES/NO SQL representations */
  protected Integer dbTrueConst;
  protected Integer dbFalseConst;
  /** Time zone & format for recording dates into DB */
  protected String     dateRecordTZ;
  protected TimeZone   dbRecordTZ;
  protected DateFormat dbDateFormat;
  /** Date to SQL format conversion - prefix & tail strings */
  protected String dateFuncBegin;
  protected String dateFuncEnd;
  /** Application support */
  protected libApplication appSupport;
  
  
  /** Default connection constructor */
  public libDatabaseProperties(TimeZone defaultTZ)
  {
    this(null, defaultTZ);
  }
  
  /** Constructor for a given connection type - default timezone for recording dates into database given */
  public libDatabaseProperties(String sqlType, TimeZone defaultTZ)
  {
    appSupport       = libApplication.getInstance();
    typeDbServer     = getDatabaseLockType(sqlType);
    // String support    
    canHardSort      = isCanHardSortConfigured  (sqlType);
    dbRuleCollator   = getRuleBasedCollator     (sqlType);
    maxLengthChar    = getConfigIntegWithDefault(sqlType, "MAX_LENGTH_VARCHAR"   );
    dbConcat         = getConfigValueWithDefault(sqlType, "STR_CONCAT"           );
    toCharFuncBegin  = getConfigValueWithDefault(sqlType, "TOCHAR_FUNC_BEGIN"    );
    toIntFuncBegin   = getConfigValueWithDefault(sqlType, "TOINT_FUNC_BEGIN"     );
    // Decimal number support
    dbMaxDouble      = getConfigDoublWithDefault(sqlType, "DB_MAX_DOUBLE"        );
    dbMinDouble      = getConfigDoublWithDefault(sqlType, "DB_MIN_DOUBLE"        );
    dbMaxSignificant = getConfigIntegWithDefault(sqlType, "DB_MAX_SIGNIFICANT"   );
    dbSignifMargin   = getConfigIntegWithDefault(sqlType, "DB_SIGNIFICANT_MARGIN");
    // Boolean support
    dbTrueConst      = getConfigIntegWithDefault(sqlType, "DB_TRUE_CONST"        );
    dbFalseConst     = getConfigIntegWithDefault(sqlType, "DB_FALSE_CONST"       );
    // Date support
    dateFuncBegin    = getConfigValueWithDefault(sqlType, "DATE_FUNC_BEGIN"      );
    dateFuncEnd      = getConfigValueWithDefault(sqlType, "DATE_FUNC_END"        );
    dateRecordTZ     = getConfigValueWithDefault(sqlType, "DB_RECORD_TZ"         );
    dbRecordTZ       = dateRecordTZ != null ? getTimeZone(dateRecordTZ) : defaultTZ;
    dbDateFormat     = getNativeDateFormat(dbRecordTZ);
    
    // Debug
    debug("DB type         = " + typeDbServer    );
    debug("canHardSort     = " + canHardSort     );
    debug("collator rules  = " +(dbRuleCollator != null ? dbRuleCollator.getRules() : ""));
    debug("maxLengthChar   = " + maxLengthChar   );
    debug("dbConcat        = " + dbConcat        );
    debug("toCharFuncBegin = " + toCharFuncBegin );
    debug("toIntFuncBegin  = " + toIntFuncBegin  );
    debug("dbMaxDouble     = " + dbMaxDouble     );
    debug("dbMinDouble     = " + dbMinDouble     );
    debug("dbMaxSignificant= " + dbMaxSignificant);
    debug("dbSignifMargin  = " + dbSignifMargin  );
    debug("dbTrueConst     = " + dbTrueConst     );
    debug("dbFalseConst    = " + dbFalseConst    );
    debug("dateFuncBegin   = " + dateFuncBegin   );
    debug("dateFuncEnd     = " + dateFuncEnd     );
    debug("dateRecordTZ    = " + dateRecordTZ    );
    debug("dbRecordTZ      = " + dbRecordTZ.getID());   
  }
  
  /** Config parameter from key using default connection as default */
  protected String getConfigValueWithDefault(String sqlType, String key)
  {
    String result = appSupport.getConfigValue(getKeyForType(sqlType, key));
    if (result == null && sqlType != null)
      result = appSupport.getConfigValue(key);
    return result;  
  }
  
  /** Config integer parameter from key using default connection as default - 
      use default if none of them defined 
    */
  protected int getConfigIntegWithDefault(String sqlType, String key, int defaultValue)
  {
    Integer value =  getConfigIntegWithDefault(sqlType, key);
    return value != null ? value.intValue() : defaultValue;
  }
  
  /** Config integer parameter from key using default connection as default - 
      use default if none of them defined 
    */
  protected Integer getConfigIntegWithDefault(String sqlType, String key)
  {
    Integer result = appSupport.getConfigInteger(getKeyForType(sqlType, key));
    if (result == null && sqlType != null)
      result = appSupport.getConfigInteger(key);
    return result;  
  }
  
  /** Config double parameter from key using default connection as default - 
      use default if none of them defined 
    */
  protected Double getConfigDoublWithDefault(String sqlType, String key)
  {
    Double result = appSupport.getConfigDouble(getKeyForType(sqlType, key));
    if (result == null && sqlType != null)
      result = appSupport.getConfigDouble(key);
    return result;  
  }
  
  /** Config parameter key name for a given database type */
  protected String getKeyForType(String typeName, String key)
  {
    if (typeName != null)
      key = key + "_" + typeName.toUpperCase();
    return key;  
  }

  /** Database type - determines how record-lock query is built */
  public int getDatabaseLockType(String sqlType)
  {
    // Configuration keys are different for default connection & external ones
    Integer lockType = appSupport.getConfigInteger(sqlType == null ? "TYPE_DATABASE_SERVER"
                                        : getKeyForType(sqlType, "SQL_LOCK_ROW_TYPE"));
    return lockType != null ? lockType.intValue() : JDBC_FOR_UPDATE;                                                               
  }
  
  /** Return if the connection can do the hard sort option */
  protected boolean isCanHardSortConfigured(String typeName)
  {
    Integer canHardSortConfig = appSupport.getConfigInteger(getKeyForType(typeName, "CAN_HARD_SORT"));
    return canHardSortConfig != null ? canHardSortConfig.intValue() == 1
                                     : typeDbServer == ORACLE;  // Oracle hard sort is OK by default
  }

  /** Return the RuleBasedCollator which will manage all the sorts
    * in the database fields. */
  protected RuleBasedCollator getRuleBasedCollator(String typeName)
  {
    RuleBasedCollator dbRuleCollator = null;
    try
    {
      String ruleSortCollections = "";
      Integer numCollect = appSupport.getConfigInteger(getKeyForType(typeName, "NUMBER_SORT_COLLECTIONS"));
      int numberCollections = numCollect != null ? numCollect.intValue() : 0;
      for (int j = 0; j < numberCollections; j++)
        ruleSortCollections += appSupport.getConfigValue(getKeyForType(typeName, "SORT_COLLECTION") + "_" + j);

      if (!ruleSortCollections.equals(""))
        dbRuleCollator = new RuleBasedCollator(ruleSortCollections);
    }
    catch(Exception except)
    {
      appSupport.showErrorMsg(except);
    }
    return dbRuleCollator;
  }
  
  
  //
  // Property getters - database methods
  //
  /** Database type - determines how record-lock query is built (see buildSqlLockRow()) 
                    & main connection JDBC URL - return value according to enumeration on top
    */
  public int getDbType()
  {
    return typeDbServer;
  }
  
  /** Gets the SELECT statement to lock a row in the server from standard SELECT */
  public String buildSqlLockRow(String sql)
  {
    String result = sql;

    switch (typeDbServer)
    {
      case JDBC_FOR_UPDATE:
        result+= " for update";
        break;
      case ORACLE: 
        result+= " for update nowait";
        break;
      case MSSQL: 
        int pos = result.toUpperCase().indexOf(" WHERE ");
        result = sql.substring(0,pos) + "(UPDLOCK)" + sql.substring(pos);
        break;
    }
    return result;
  }
  
  /** Sorting collator sequence */
  public RuleBasedCollator getCurrentRuleCollator()
  {
    return dbRuleCollator;
  }

  /** DB (hard) sorting is compatible with Java sorting rules */
  public boolean isCanHardSort()
  {
    return canHardSort;
  }

  /** String maximum length */
  public Integer getMaxLengthChar()
  {
    return maxLengthChar;
  }

  /** String concatenation operator */
  public String getDbStrConcat()
  {
    return dbConcat;
  }

  /** Function prefix for non-string data type object to string conversion */
  public String getToCharFuncBegin()
  {
    return toCharFuncBegin;
  }

  /** Function prefix for string data type object to integer number conversion */
  public String getToIntFuncBegin()
  {
    return toIntFuncBegin;
  }

  /** Maximum allowed for decimal number in database */
  public Double getDbMaxDouble()
  {
    return dbMaxDouble;
  }

  /** Minimum allowed for decimal number in database */
  public Double getDbMinDouble()
  {
    return dbMinDouble;
  }

  /** Number of digital figures decimal numbers should be rounded to - see lib.Util.libNormalizeNumber */
  public Integer getDbMaxSignificant()
  {
    return dbMaxSignificant;
  }

  /** Number of non-significant last decimal figures for rounding - see lib.Util.libNormalizeNumber */
  public Integer getDbSignifMargin()
  {
    return dbSignifMargin;
  }

  /** Boolean YES SQL string representation in the connection */
  public Number getDbTrueConst()
  {
    return dbTrueConst;
  }
  
  /** Boolean NO SQL string representation in the connection */
  public Number getDbFalseConst()
  {
    return dbFalseConst;
  }
  
  /** Timestamp SQL string representation in the connection*/
  public String getDbSQLDate(Timestamp obj)
  {
    return obj != null ? getDateFuncBegin() + dbDateFormat.format(obj) + getDateFuncEnd() : null;
  }
  
  /** Date SQL string format prefix */
  public String getDateFuncBegin()
  {
    return dateFuncBegin != null ? dateFuncBegin : "'";
  }

  /** Date SQL string format tail */
  public String getDateFuncEnd()
  {
    return dateFuncEnd != null ? dateFuncEnd : "'";
  }

  /** Time zone for recording dates into DB */
  public TimeZone getDbRecordTZ()
  {
    return dbRecordTZ;
  }

  /** Format for recording dates into DB */
  public DateFormat getDbDateFormat()
  {
    return dbDateFormat;
  }
  
  //
  // Debug utilities
  //
  /** Debug utility method */
  protected void debug(String msg)
  {
    libFileDebug.getInstance().write("libDatabaseProperties." + msg);
  }
  
  //
  // Static support
  //
  
  /** Get time zone utility method - returns default timezone if invalid id is passed */
  public static TimeZone getTimeZone(String id)
  {
    TimeZone result = TimeZone.getTimeZone(id);  // Returns "GMT" if id invalid
    if (result.getID().equals("GMT") && !id.equals("GMT"))
      result = TimeZone.getDefault();
    return result;
  }

  /** Native (DB) date format for given time zone */
  public static DateFormat getNativeDateFormat(TimeZone tz)
  {
    SimpleDateFormat result = new SimpleDateFormat(NATIVE_DATE_FORMAT);
    result.setTimeZone(tz);
    
    return result;
  }
  
}
